# -*- coding:utf-8 -*-

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
SQLALCHEMY_DATABASE_URI = "mysql://%s:%s@%s:%d/%s" % ('root', 'ablejava', '192.168.9.223', 3306,'zhw-test')
app.config['SQLALCHEMY_DATABASE_URI'] = SQLALCHEMY_DATABASE_URI
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)


class Author(db.Model):
    __tablename__ = 'authors'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    books = db.relationship('Book', backref='author')

    def __repr__(self):
        return 'Author:%s' % self.name


class Book(db.Model):
    __tablename__ = 'books'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    author_id = db.Column(db.Integer, db.ForeignKey('authors.id'))

    def __repr__(self):
        return 'Book:%s,%s' % (self.name, self.author_id)


@app.route('/')
def hello_world():
    return 'Hello World!'


if __name__ == '__main__':
    # 删除所有的表
    db.drop_all()
    # 创建数据表
    db.create_all()

    # 生成数据
    au1 = Author(name='老王')
    au2 = Author(name='老惠')
    au3 = Author(name='老刘')
    # 把数据提交给用户会话
    db.session.add_all([au1, au2, au3])
    # 提交会话
    db.session.commit()
    bk1 = Book(name='老王回忆录', author_id=au1.id)
    bk2 = Book(name='我读书少，你别骗我', author_id=au1.id)
    bk3 = Book(name='如何才能让自己更骚', author_id=au2.id)
    bk4 = Book(name='怎样征服美丽少女', author_id=au3.id)
    bk5 = Book(name='如何征服英俊少男', author_id=au3.id)
    bk5 = Book(name='test', author_id=au3.id)
    # 把数据提交给用户会话
    db.session.add_all([bk1, bk2, bk3, bk4, bk5])
    # 提交会话
    db.session.commit()

    # 查询操作
    # 查询表内所有内容
    res = Book.query.all()
    print("查询表内所有内容" + str(res))
    # 只显示第一个
    res = Book.query.first()
    print("只显示第一个" + str(res))
    # 按照条件查询
    res = Book.query.filter_by(name='老王回忆录').all()
    print("按照条件查询" + str(res))
    # 逻辑非
    res = Book.query.filter(Book.name != '老王回忆录').all()
    print('逻辑非' + str(res))
    # 取反
    from sqlalchemy import not_

    res = Book.query.filter(not_(Book.name != 'test')).all()
    print('取反' + str(res))
    # 逻辑与
    from sqlalchemy import and_

    res = Book.query.filter(and_(Book.id == 3, Book.name == 'test')).all()
    print('与' + str(res))
    # 逻辑或
    from sqlalchemy import or_

    res = Book.query.filter(or_(Book.name == 'guest', Book.name == 'test')).all()
    print('与' + str(res))

    app.run(debug=True)
